import collections
import os

import numpy as np
import pandas as pd

import openpyxl
from openpyxl.styles import PatternFill
from openpyxl.styles import Font
from openpyxl.styles import Alignment
from openpyxl.drawing.image import Image
from autoWidth import CXlAutofit
import matplotlib.pyplot as plt
import matplotlib.ticker as mtick
from datetime import datetime
import statistics

columns = ['Inventory Asset', 'Date', 'Transaction Type', 'Num', 'Name', 'Memo/Description', 'Qty', 'Rate',
           'Amount',
           'Balance']

centerAli = Alignment(horizontal='center', vertical='center')
font1 = Font(
    name="宋体",  # 字体
    size=18,  # 字体大小
    # color="0000FF",  # 字体颜色，用16进制rgb表示
    bold=True,  # 是否加粗，True/False
    italic=False,  # 是否斜体，True/False
    strike=None,  # 是否使用删除线，True/False
    underline=None,  # 下划线, 可选'singleAccounting', 'double', 'single', 'doubleAccounting'
)
font2 = Font(
    name="宋体",  # 字体
    size=12,  # 字体大小
    # color="0000FF",  # 字体颜色，用16进制rgb表示
    bold=True,  # 是否加粗，True/False
    italic=False,  # 是否斜体，True/False
    strike=None,  # 是否使用删除线，True/False
    underline=None,  # 下划线, 可选'singleAccounting', 'double', 'single', 'doubleAccounting'
)
font3 = Font(
    name="宋体",  # 字体
    size=12,  # 字体大小
    # color="0000FF",  # 字体颜色，用16进制rgb表示
    bold=False,  # 是否加粗，True/False
    italic=False,  # 是否斜体，True/False
    strike=None,  # 是否使用删除线，True/False
    underline=None,  # 下划线, 可选'singleAccounting', 'double', 'single', 'doubleAccounting'
)

baseColumn = {1: 'A', 2: 'B', 3: 'C', 4: 'D', 5: 'E', 6: 'F', 7: 'G', 8: 'H', 9: 'I', 10: 'J', 11: 'K', 12: 'L',
              13: 'M', 14: 'N', 15: 'O', 16: 'P', 17: 'Q', 18: 'R', 19: 'S', 20: 'T', 21: 'U', 22: 'V', 23: 'W',
              24: 'X', 25: 'Y', 26: 'Z', 27: 'AA', 28: 'AB', 29: 'AC', 30: 'AD', 31: 'AE', 32: 'AF', 33: 'AG',
              34: 'AH', 35: 'AI', 36: 'AJ', 37: 'AK', 38: 'AL', 39: 'AM', 40: 'AN', 41: 'AO', 42: 'AP', 43: 'AQ',
              44: 'AR', 45: 'AS', 46: 'AT', 47: 'AU', 48: 'AV', 49: 'AW', 50: 'AX', 51: 'AY', 52: 'AZ', 53: 'BA',
              54: 'BB', 55: 'BC', 56: 'BD', 57: 'BE', 58: 'BF', 59: 'BG', 60: 'BH', 61: 'BI', 62: 'BJ', 63: 'BK',
              64: 'BL', 65: 'BM', 66: 'BN', 67: 'BO', 68: 'BP', 69: 'BQ', 70: 'BR', 71: 'BS', 72: 'BT', 73: 'BU',
              74: 'BV', 75: 'BW', 76: 'BX', 77: 'BY', 78: 'BZ', 79: 'CA', 80: 'CB', 81: 'CC', 82: 'CD', 83: 'CE',
              84: 'CF', 85: 'CG', 86: 'CH', 87: 'CI', 88: 'CJ', 89: 'CK', 90: 'CL', 91: 'CM', 92: 'CN', 93: 'CO',
              94: 'CP', 95: 'CQ', 96: 'CR', 97: 'CS', 98: 'CT', 99: 'CU', 100: 'CV', 101: 'CW', 102: 'CX', 103: 'CY',
              104: 'CZ', 105: 'DA', 106: 'DB', 107: 'DC', 108: 'DD', 109: 'DE', 110: 'DF', 111: 'DG', 112: 'DH',
              113: 'DI', 114: 'DJ', 115: 'DK', 116: 'DL', 117: 'DM', 118: 'DN', 119: 'DO', 120: 'DP', 121: 'DQ',
              122: 'DR', 123: 'DS', 124: 'DT', 125: 'DU', 126: 'DV', 127: 'DW', 128: 'DX', 129: 'DY', 130: 'DZ',
              131: 'EA', 132: 'EB', 133: 'EC', 134: 'ED', 135: 'EE', 136: 'EF', 137: 'EG', 138: 'EH', 139: 'EI',
              140: 'EJ', 141: 'EK', 142: 'EL', 143: 'EM', 144: 'EN', 145: 'EO', 146: 'EP', 147: 'EQ', 148: 'ER',
              149: 'ES', 150: 'ET', 151: 'EU', 152: 'EV', 153: 'EW', 154: 'EX', 155: 'EY', 156: 'EZ', 157: 'FA',
              158: 'FB', 159: 'FC', 160: 'FD', 161: 'FE', 162: 'FF', 163: 'FG', 164: 'FH', 165: 'FI', 166: 'FJ',
              167: 'FK', 168: 'FL', 169: 'FM', 170: 'FN', 171: 'FO', 172: 'FP', 173: 'FQ', 174: 'FR', 175: 'FS',
              176: 'FT', 177: 'FU', 178: 'FV', 179: 'FW', 180: 'FX', 181: 'FY', 182: 'FZ', 183: 'GA', 184: 'GB',
              185: 'GC', 186: 'GD', 187: 'GE', 188: 'GF', 189: 'GG', 190: 'GH', 191: 'GI', 192: 'GJ', 193: 'GK',
              194: 'GL', 195: 'GM', 196: 'GN', 197: 'GO', 198: 'GP', 199: 'GQ', 200: 'GR', 201: 'GS', 202: 'GT',
              203: 'GU', 204: 'GV', 205: 'GW', 206: 'GX', 207: 'GY', 208: 'GZ'}


def compare_time(keyTime, curTime):
    time = keyTime.split('-')
    year1 = int(time[0])
    month1 = int(time[1])
    curTimeArr = curTime.split('-')
    year2 = int(curTimeArr[0])
    month2 = int(curTimeArr[1])
    return (year1 * 12 + month1) > year2 * 12 + month2


'''
用于查询过保质期有多少数量
store_amount-> 库存剩余量
store_time -> 过期时间
billDic -> bill字典
'''


def calculateStoreDate(store_amount, store_time, billDic, timeLine):
    # timeLine.reverse()
    store_dic = {}
    now = datetime.now()
    cur_year = now.year
    cur_month = now.month
    finalTime = ''
    for index, row in enumerate(timeLine):
        if billDic[row] == 0:
            continue
        billTime = row.split('-')
        year = int(billTime[0])
        month = int(billTime[1])
        num_temp = int(billDic[row])
        cur_num = num_temp
        if store_amount < num_temp:
            cur_num = store_amount
        # 保质期 - （当前时间 - 生产时间）
        get_remain_month = str(store_time - calculateMonth(cur_year, cur_month, year, month)) + '个月'
        finalTime = get_remain_month
        numKeyName = str(cur_num) + '箱'
        store_dic[numKeyName] = get_remain_month
        # 这边过保护期需要怎么考虑呢？
        store_amount -= cur_num
        if store_amount == 0:
            break
    if store_amount != 0:
        store_dic[str(store_amount) + '箱'] = '<' + str(finalTime) + ',请将起始年份提前，获取更多数据'
    return store_dic


def calculateMonth(year1, month1, year2, month2):
    return year1 * 12 + month1 - year2 * 12 - month2


'''
 sheet
 rows 到哪行了
 columnIndex 到哪列了
 stationList excel中各列组成的一个Dic -》从1开始
 dicForSecTable 值集合
 tableTitle 表格标题
 isMiddle=False 第二个表格的第二列及第三列控制器
 isDescription=False 单元格是否标黄
'''


def fillTableBelowMain(sheet, rows, columnIndex, stationList, dicForSecTable, tableTitle, timeLine,
                       isDescription=False, color='fdfc45', isDemoOne=False):
    sheet.merge_cells(stationList[columnIndex] + str(rows) + ':' + stationList[columnIndex + 1] + str(rows))
    optionYxl(sheet, stationList[columnIndex] + str(rows), tableTitle, font2, False)
    rows += 1
    for idx, dateTime in enumerate(timeLine):
        keysList = dicForSecTable.keys()
        if dicForSecTable[dateTime] == 0:
            if isDemoOne:
                continue
            else:
                value = 0
        else:
            value = dicForSecTable[dateTime]
        optionYxl(sheet, stationList[columnIndex] + str(rows), dateTime, font2)
        optionYxl(sheet, stationList[columnIndex + 1] + str(rows), value, font3,
                  isDescription=isDescription,
                  color=color)
        rows += 1


'''
    主要用于操作单元格，给单元格赋值都用这个函数
    OptionSheet: 给定的工作表
    cellName: 单元格位置 -》 B1等
    value:单元格的值
    font: 字体，分为标题，描述以及表格内的数值
    mergeFlag: 是否融合 
    mergeStart=0:融合起始位置 
    mergeEnd=0:融合结束位置
    cellWidth=50:单元格宽度
    isDescription=False: 是否是商品描述
    columnName='B': 调整单元格宽度
'''


def optionYxl(OptionSheet, cellName, value, font, mergeFlag=False, mergeStart=0, mergeEnd=0, cellWidth=50,
              isDescription=False, columnName='B', color='fce4d3'):
    if mergeFlag:
        OptionSheet.merge_cells(mergeStart + ':' + mergeEnd)
    OptionSheet[cellName] = value
    if isDescription:
        OptionSheet[cellName].fill = PatternFill(start_color=color, end_color=color, fill_type='solid')
    OptionSheet[cellName].font = font
    OptionSheet[cellName].alignment = centerAli
    # OptionSheet.column_dimensions['B'].width = 1000.0
    # OptionSheet.column_dimensions[columnName].width = cellWidth
    # 两层循环遍历所有有数据的单元格
    # img = Image('a.jpg')
    # img.width = 1310
    # img.height = 400
    # OptionSheet.add_image(img, 'A51')


'''
    fileName: 输出文件地址
    startYear, startMonth, endMonth, endYear -》 过滤年份
    return: dic:带有四个数据的字典, shopDescription：商品名字, 
    maxKey：最晚日期, minKey：最早日期 -》后面两个用于创建表格时间线，即只要表格中出现过的日期
'''


def cleanData(data, startYear, startMonth, endYear, endMonth):
    minKey = '9999-12'
    maxKey = str(endYear) + '-' + str(endMonth)

    # 库存
    store = 0

    billDicForTable = {}
    data = data.fillna('null')
    flag = 0
    #  四个数据
    dic = {}
    saleAndCost = {}
    saleAndCost['sale'] = {}
    saleAndCost['cost'] = {}
    dic['Invoice'] = {}
    dic['Credit Memo'] = {}
    dic['Inventory Qty Adjust'] = {}
    dic['Bill'] = {}
    # dic['productIncomeInvoiceDict'] = {}
    isTableOne = True
    shopDescription = ''
    for index, row in data.iterrows():
        inventoryAsset = data.at[index, 'Inventory Asset']
        if inventoryAsset == 'Total for Sales of Product Income':
            break
        if inventoryAsset == 'Sales of Product Income':
            isTableOne = False
        if (data.at[index, 'Rate'] == 'null') or data.at[index, 'Transaction Type'] == 'Inventory Starting Value':
            continue
        # 过滤年份
        dataTime = data.at[index, 'Date']
        if dataTime == 'null':
            continue
        timeSplit = dataTime.split('/')
        year = int(timeSplit[2])
        month = int(timeSplit[0])
        if year * 12 + month < startYear * 12 + startMonth or year * 12 + month > endYear * 12 + endMonth:
            continue
        dictKey = str(year) + '-' + str(month)
        if compare_time(minKey, dictKey):
            minKey = dictKey
        # if compare_time(dictKey, maxKey):
        #     maxKey = dictKey
        dictValue = data.at[index, 'Qty']
        if data.at[index, 'Amount'] == 'null':
            tolalPrice = 0
        else:
            tolalPrice = round(float(data.at[index, 'Amount']), 2)
        transactionType = data.at[index, 'Transaction Type']
        if transactionType == 'Vendor Credit':
            transactionType = 'Credit Memo'
        if isTableOne:
            if dictValue < 0:
                dictValue = -dictValue
            if transactionType == 'Invoice':
                totalPriceTemp = saleAndCost['cost']
                tolalPriceTemp = fillDict(totalPriceTemp, dictKey, -tolalPrice)
                saleAndCost['cost'] = tolalPriceTemp
            dicTemp = dic[transactionType]
            dicTemp = fillDict(dicTemp, dictKey, dictValue)
            dic[transactionType] = dicTemp

        # productIncome表格中，只统计invoice
        elif transactionType == 'Invoice':
            totalPriceTemp = saleAndCost['sale']
            tolalPriceTemp = fillDict(totalPriceTemp, dictKey, tolalPrice)
            saleAndCost['sale'] = tolalPriceTemp
            # productIncomeInvoiceDict = fillDict(productIncomeInvoiceDict, dictKey, -dictValue)

    endYear = int(maxKey.split('-')[0])
    endMonth = int(maxKey.split('-')[1])
    startYear = int(minKey.split('-')[0])
    startMonth = int(minKey.split('-')[1])
    tableColumnsIn = ColumnPadding(startYear, startMonth, endYear, endMonth)
    dic['Invoice'] = padding(dic['Invoice'], tableColumnsIn)
    dic['Credit Memo'] = padding(dic['Credit Memo'], tableColumnsIn)
    dic['Bill'] = padding(dic['Bill'], tableColumnsIn)
    dic['Inventory Qty Adjust'] = padding(dic['Inventory Qty Adjust'], tableColumnsIn)
    saleAndCost['sale'] = padding(saleAndCost['sale'], tableColumnsIn)
    saleAndCost['cost'] = padding(saleAndCost['cost'], tableColumnsIn)
    return dic, shopDescription, maxKey, minKey, billDicForTable, saleAndCost


'''
    用于补全四个数据中的空缺数据，这个方法在cleanData后紧跟，补全数据
'''


def padding(dic, tableColumns):
    for key in tableColumns:
        if not dic.__contains__(key):
            dic[key] = 0
    return dic


'''
    判断字典中的这个key存不存在，存在就累计，不存在就创建
'''


def fillDict(dictFillDict, key, value):
    if dictFillDict.__contains__(key):
        if isinstance(value, float):
            dictFillDict[key] = round(dictFillDict[key] + value, 2)
        else:
            dictFillDict[key] += value
    else:
        dictFillDict[key] = value
    return dictFillDict


'''
    返回创建好文件的sheet
'''


def openyxl_createFile(fileName):
    workbook = openpyxl.Workbook()
    sheetCreate = workbook.active
    workbook.save(fileName)
    # return sheet


def modGet(n):
    ret = []
    while n > 0:
        mod = n % 27
        n = n // 27
        ret.append(mod)
    ret.reverse()
    return ret


'''
    单元格中的位置
'''


def getLocationName():
    return baseColumn


'''
    前面那个option主要填充描述列名信息，下方这个则是填充表格内的数据
    dicSelf:改列对应的dic，如Invoice等
    sheetIn:操作的工作簿
    rows:到哪一行了
    columns:到哪一列了
    startYearIn:
    startMonthIn
    endYearIn
    endMonthIn
    locationDic:表格中的位置
'''


def fillTableData(dicSelf, sheetIn, rows, columns, ColumnList, locationDic):
    for tempKey in ColumnList:
        optionYxl(sheetIn, locationDic[columns] + str(rows), dicSelf[tempKey], font3, False)
        columns += 1
    # 写入总计
    allSum = sum(list(dicSelf.values()))
    optionYxl(sheetIn, locationDic[columns] + str(rows), allSum, font3, False)


# 获取column年份,只要表格中有的最早的日期到最晚的日期
def ColumnPadding(startYearIn, startMonthIn, endYearIn, endMonthIn):
    arr = []
    arr.append(str(startYearIn) + '-' + str(startMonthIn))
    if str(startYearIn) + '-' + str(startMonthIn) == str(endYearIn) + '-' + str(endMonthIn):
        return arr
    while True:
        startYearIn += (startMonthIn + 1) // 13
        startMonthIn = (startMonthIn + 1) % 13
        if startMonthIn == 0:
            startMonthIn = 1
        tempKey = str(startYearIn) + '-' + str(startMonthIn)
        arr.append(tempKey)
        if tempKey == str(endYearIn) + '-' + str(endMonthIn):
            break
    return arr


'''
    Invoice = Invoice + credit Memo 
    所以需要重新添加函数
'''


def changeInvoice(dicInvoice, dicCreditMemo):
    keys = dicInvoice.keys()
    for key in keys:
        dicInvoice[key] += dicCreditMemo[key]
    return dicInvoice


'''
    倒推库存
    需要思考-》 当前库存指的是否是
'''


def recoverStore(invoiceDic, BillDic, columnList, store=500):
    # columnList.reverse()
    storeList = []
    storeDic = {}
    storeList.append(store)
    for idx, dateTime in enumerate(columnList):
        if idx == len(columnList) - 1:
            break
        store += invoiceDic[dateTime] - BillDic[dateTime]
        if store < 0:
            storeList.append(0)
        else:
            storeList.append(store)
    storeList.reverse()
    reverseColumn = columnList.copy()
    reverseColumn.reverse()
    for idx, dateTime in enumerate(reverseColumn):
        storeDic[dateTime] = storeList[idx]
    return floatToInt(storeList), storeDic


'''
    拿到percent
'''


def getPercent(InvoiceList, bill):
    percentList = []
    for invoice in InvoiceList:
        percent = dividMyself(invoice, bill, True)
        if percent < 0:
            percent = 0
        percentList.append(percent)
    return percentList


'''
    画图A
'''


def drawFig1(columns=["A", "B", "C", "D", "E"], bill=[10, 0, 30, 40, 50]):
    # 模拟数据

    x = np.array(columns)
    y = np.array(bill)
    # 绘制柱状图
    plt.figure(figsize=(12, 4))
    plt.bar(x, y)
    xticks_pos = [i + 2 for i in range(len(x))]
    # 设置标题和坐标轴标签
    plt.title("Bill")
    # plt.figure(figsize=(12, 12))
    # 添加 X 轴标签
    # x = np.array(columns)
    plt.xticks(x, [f"{x[i]}\nbill:{y[i]}" for i in range(len(x))])
    # 添加数值标签
    for i, v in enumerate(y):
        plt.text(i, v + 1, str(v), ha="center")

    plt.savefig('a.jpg')
    # 显示图形
    # plt.show()


'''
    float转int
'''


def floatToInt(floatList):
    intList = []
    for i in floatList:
        intList.append(int(i))
    return intList


'''
    从字典中拿到有序的list
'''


def getListFromDic(dic, columnsListt):
    ret = []
    for column in columnsListt:
        value = dic[column]
        ret.append(int(value))
    return ret


'''
    往excel中插入图1
'''


def insertFigToExcel(sheetIn, columnStation, rows, figName):
    img = Image(figName)
    img.width = 1200
    img.height = 600
    sheetIn.add_image(img, columnStation + str(rows))


def draw2(invoice=[], percent=[], store=[], columnList=[], figName='g.jpg'):
    # invoice = [1228.3, 3.38, 63.8, 0.07, 0.16, 6.74, 1896.18]  # 数据
    # percent = [0.12, -12.44, 1.82, 16.67, 6.67, -6.52, 4.04]
    # store = [4500, 50, 200, 70, 100, 28, 2000]
    # columnList = [u'一月', u'二月', u'三月', u'四月', u'五月', u'六月', u'七月']
    l = [i for i in range(len(columnList))]

    fmt = '%.2f%%'
    yticks = mtick.FormatStrFormatter(fmt)  # 设置百分比形式的坐标轴

    fig = plt.figure(figsize=(12, 6))
    ax1 = fig.add_subplot(111)
    ax1.plot(l, percent, 'or-', label=u'百分比');
    ax1.yaxis.set_major_formatter(yticks)
    for i, (_x, _y) in enumerate(zip(l, percent)):
        ax1.text(_x, _y, percent[i], color='black', fontsize=12)  # 将数值显示在图形上

    ax1.legend(loc=2)
    ax1.set_ylim([0, 30]);
    ax1.set_ylabel('Percent');
    ax2 = ax1.twinx()  # this is the important function
    ax2.plot(l, store, marker='*', label=u'库存');
    for i, (_x, _y) in enumerate(zip(l, store)):
        ax2.text(_x, _y, store[i], color='steelblue', fontsize=12, )  # 将数值显示在图形上

    ax2.bar(l, invoice, alpha=0.3, facecolor="#9999ff", edgecolor="white", label=u'销量')
    for x1, yy in zip(l, invoice):
        ax2.text(x1, yy + 1, str(yy), ha='center', va='bottom', fontsize=12, rotation=0)

    ax2.legend(loc=1)
    x = np.array(columnList)
    # plt.xticks(l, [f"{x[i]}\nsale:{invoice[i]}\nstore:{store[i]}" for i in range(len(x))], fontsize=12)
    plt.xticks(l, columnList)
    plt.savefig(figName)
    plt.show()


# 图 2 3
def drawOne(dataLeft1=[], labeLeft1='label1', dataLeft2=[], labelLeft2='label2', dataLeft3=[], labelLeft3='label3',
            dataRight1=[], labelRight1='label4', columnList=[], figName='g.jpg', isDrawOne=False, color1='#2f8be7',
            color2='#92ace0', color3='#f8cdac', color4='green'):
    num1 = 1.01
    num2 = 0
    num3 = 3
    num4 = 0
    l = [i for i in range(len(columnList))]
    x = np.arange(len(l))
    fig = plt.figure(figsize=(12, 6))
    ax1 = fig.add_subplot(111)
    width = 0.35
    rects1 = ax1.bar(x - width / 2, dataLeft1, width, label=labeLeft1, color=color1)
    autolabel(rects1, ax1)
    if len(dataLeft2) != 0:
        rects2 = ax1.bar(x + width / 2, dataLeft2, width, label=labelLeft2, color=color2)
        autolabel(rects2, ax1)
    ax1.plot(l, dataLeft3, label=labelLeft3, color=color3)
    for i, (_x, _y) in enumerate(zip(l, dataLeft3)):
        valueBar = int(0)
        if dataLeft3[i] != 0:
            valueBar = dataLeft3[i]
        ax1.text(_x, _y, valueBar, color='black', fontsize=10)  # 将数值显示在图形上
    ax1.legend(bbox_to_anchor=(num1, num2), loc=2, borderaxespad=num4)

    if isDrawOne:
        fmt = '%.2f%%'
        yticks = mtick.FormatStrFormatter(fmt)  # 设置百分比形式的坐标轴
        ax2 = ax1.twinx()  # this is the important function
        ax2.plot(l, dataRight1, label=labelRight1, color=color4)
        ax2.yaxis.set_major_formatter(yticks)
        for i, (_x, _y) in enumerate(zip(l, dataRight1)):
            value = int(0)
            if dataRight1[i] != 0:
                value = str(dataRight1[i]) + '%'
            ax2.text(_x, _y, value, color='black', fontsize=10)  # 将数值显示在图形上
        # ax2.legend(loc=2)
    ax2.legend(bbox_to_anchor=(num1, num2), loc=num3, borderaxespad=num4)

    listIn = []
    for i in columnList:
        listIn.append(i.split('-')[1])

    plt.xticks(l, listIn)
    plt.savefig(figName)
    # plt.show()


# 柱状图上显示出数据
def autolabel(rects, ax):
    """Attach a text label above each bar in *rects*, displaying its height."""
    for rect in rects:
        height = rect.get_height()
        ax.annotate('{}'.format(height),
                    xy=(rect.get_x() + rect.get_width() / 2, height),
                    xytext=(0, 3),  # 3 points vertical offset
                    textcoords="offset points",
                    ha='center', va='bottom')


'''
    拿到percent的字典，为了写入table中
'''


def getPercentDic(percentList, columnsList):
    dicIn = {}
    for idx, column in enumerate(columnsList):
        dicIn[column] = percentList[idx]
    return dicIn


'''
用于填保质期的函数
'''


def fillRemainTimeTable(sheet, outRows, columnIndex, locationDic, remainTimeDic):
    for index, keyName in enumerate(remainTimeDic.keys()):
        optionYxl(sheet, locationDic[columnIndex] + str(outRows + index), keyName, font2)
        optionYxl(sheet, locationDic[columnIndex + 1] + str(outRows + index), remainTimeDic[keyName], font3)


'''
填销售额和平均销售额
'''


def fillTable(sheet, columnIndex, rows, titleName, columnNameList, timelineList, dataDic, stationDic,
              titleColor='f2b7af'):
    sheet.merge_cells(
        stationDic[columnIndex] + str(rows) + ':' + stationDic[columnIndex + (len(timelineList))] + str(rows))
    optionYxl(sheet, stationDic[columnIndex] + str(rows), titleName, font1, isDescription=True, color=titleColor)
    rows += 2
    yearSet = set()
    yearSet.add('a')
    # 第一列
    for idx, value in enumerate(columnNameList):
        optionYxl(sheet, stationDic[columnIndex] + str(rows + 1 + idx), columnNameList[idx], font2)
    # 时间线
    for idx, value in enumerate(timelineList):
        timeArr = value.split('-')
        titleYear = timeArr[0]
        titleMonth = timeArr[1]
        if yearUniqueAdd(yearSet, titleYear):
            optionYxl(sheet, stationDic[columnIndex + 1 + idx] + str(rows - 1), titleYear, font2)
        optionYxl(sheet, stationDic[columnIndex + 1 + idx] + str(rows), titleMonth, font2)
    columnIndex += 2
    rows += 1
    # 剩余表格
    for idx, value in enumerate(columnNameList):
        dicTemp = dataDic[value]
        for timeLineIdx, key in enumerate(timelineList):
            if key in dicTemp.keys():
                valueOption = dicTemp[key]
                if (value == 'Percent/%' or value == '利润占比') and valueOption != 0:
                    valueOption = str(valueOption) + '%'
                optionYxl(sheet, stationDic[columnIndex + timeLineIdx - 1] + str(rows), valueOption, font3)

        rows += 1


def fillTableTransposition(sheet, columnIndex, rows, titleName, columnNameList, timelineList, dataDic, stationDic,
                           titleColor='f2b7af'):
    sheet.merge_cells(
        stationDic[columnIndex] + str(rows) + ':' + stationDic[columnIndex + (len(columnNameList)) + 1] + str(rows))
    optionYxl(sheet, stationDic[columnIndex] + str(rows), titleName, font1, isDescription=True, color=titleColor)
    rows += 1
    columnIndex += 1
    yearSet = set()
    yearSet.add('a')

    # 第一列
    for idx, value in enumerate(timelineList):
        timeArr = value.split('-')
        titleYear = timeArr[0]
        titleMonth = timeArr[1]
        if yearUniqueAdd(yearSet, titleYear):
            optionYxl(sheet, stationDic[columnIndex - 1] + str(rows + idx + 1), titleYear, font2)
        optionYxl(sheet, stationDic[columnIndex] + str(rows + idx + 1), titleMonth, font2)
    # 第一行
    for idx, value in enumerate(columnNameList):
        optionYxl(sheet, stationDic[columnIndex + 1 + idx] + str(rows), value, font2)
    # 时间线

    rows += 1
    columnIndex += 1
    # 剩余表格
    for idx, value in enumerate(columnNameList):
        dicTemp = dataDic[value]
        for timeLineIdx, key in enumerate(timelineList):
            if key in dicTemp.keys():
                valueOption = dicTemp[key]
                if (value == 'Percent/%' or value == '利润占比') and valueOption != 0:
                    valueOption = str(valueOption) + '%'
                optionYxl(sheet, stationDic[columnIndex] + str(rows + timeLineIdx), valueOption, font3)

        columnIndex += 1


# 处理数据
def disposeData(fileName):
    dfData = pd.read_excel(fileName, engine='openpyxl')
    dfData.columns = columns
    sectionDistance = 0
    for index, rowValue in dfData.iterrows():
        sectionDistance += 1
        if rowValue['Date'] == 'Date':
            break

    return dfData.iloc[sectionDistance + 1:-1]


'''
用于计算头顶年份是否第一次出现
'''


def yearUniqueAdd(yearSet, key):
    if yearSet.__contains__(key) or key == 'total' or key == 'average':
        return False
    else:
        yearSet.add(key)
        return True


def calculateAverage(dataList):
    length = len(dataList)
    total = 0
    for value in dataList:
        if value == 0:
            length -= 1
            continue
        total += float(value)
    return dividMyself(total, length)


def dividMyself(a, b, isPercent=False):
    if b == 0:
        return 0
    if isPercent:
        return round(a / b * 100, 2)
    return round(a / b, 2)


'''
删除所有照片
'''


def removeFigList(prefixPath, fileNameList):
    for idx, fileName in enumerate(fileNameList):
        if fileName.startswith('.'):
            continue
        try:
            os.remove(prefixPath + fileName)
        except OSError as e:
            print("照片删除失败，请关闭所有已经打开的图片文件")
            return True
    return False


def calculateStoreAndExpireTime(filePath):
    df = pd.read_excel(filePath)
    columns = df.columns
    if 'description' not in columns:
        print('库存及保质期文件中第一行的descrioption标题未找到，请修改为descrioption')
    if 'store' not in columns:
        print('库存及保质期文件中第一行的store标题未找到，请修改为store')
    if 'expireTime' not in columns:
        print('库存及保质期文件中第一行的expireTime标题未找到，请修改为expireTime')
    storeAndExpireTimeDic = {}
    for idx, row in df.iterrows():
        description = row['description']
        store = float(row['store'])
        expireTime = int(row['expireTime'])
        storeAndExpireTimeDic[description] = {'store': store, 'expireTime': expireTime}
    return storeAndExpireTimeDic


def findDescription(df):
    df.fillna('null', inplace=True)
    countIdx = 0
    descriptionDic = {}
    # 统计倒数多少个商品的description
    endCount = 16
    for index, row in df.iterrows():
        if row['Rate'] == 'null':
            continue
        if row['Amount'] == 'null':
            continue
        if row['Transaction Type'] == 'Credit Memo' or row['Transaction Type'] == 'Inventory Qty Adjust':
            continue
        countIdx += 1
        #
        if countIdx == endCount:
            break
        # 记录商品名
        descriptionInTable = df.at[index, 'Memo/Description']
        if descriptionInTable != 'null':
            if descriptionInTable not in descriptionDic.keys():
                descriptionDic[descriptionInTable] = 0
            descriptionDic[descriptionInTable] += 1
    shopDescription = sorted(descriptionDic.items(), key=lambda x: x[1], reverse=True)[0][0]
    return shopDescription


# 计算字典中位数
def calculateMedia(dataCur):
    listCur = []
    for value in dataCur:
        if value == 0:
            continue
        listCur.append(value)
    return statistics.median(listCur)
